﻿using System.Data;
using SemiCASE.DataAccess;
using SemiCASE.DataType.Common.Attributes;
using System;

namespace SemiCASE.Business
{
    [ServiceConnectionNameAttribute("SemiCASEConnectionString")]
    public class ExtentionBS : BusinessBase
    {
        public DataTable LoadExtentionsFromDB(Guid pProjectID, Guid pUseCaseID)
        {
            DataTable dt = new DataTable();
            IData data = GetDataObject();
            string sqlText = @"WITH PROJECT_EXTENTIONS AS
                            (
	                            SELECT DISTINCT 
			                           MssID
                                       , ExtentionID
	                            FROM [SemiCaseDB].[dbo].[RELATION]
	                            WHERE ProjectID = @ProjectID AND UseCaseID = @UseCaseID
                            )

                             SELECT
	                            E.[Order] AS SiraNo
                                , (CASE WHEN M.[Order] IS NULL THEN '*' ELSE M.[Order] END) AS MssSiraNo 
                                , E.[ExtentionID]
                                , PE.MssID
                                , E.[Name]
                                , E.OrderSign
                              FROM [SemiCaseDB].[dbo].[EXTENTION] AS E
                                    INNER JOIN PROJECT_EXTENTIONS AS PE ON E.ExtentionID = PE.ExtentionID
                                    LEFT OUTER JOIN [SemiCaseDB].[dbo].[MSS] AS M ON M.MssID = PE.MssID
                              ORDER BY M.[Order], E.OrderSign";

            data.AddSqlParameter("ProjectID", pProjectID, SqlDbType.UniqueIdentifier);
            data.AddSqlParameter("UseCaseID", pUseCaseID, SqlDbType.UniqueIdentifier);
            data.GetRecords(dt, sqlText);
            return dt;
        }

        public void CreateExtentions(Guid pProjectID, Guid pUseCaseID, DataTable dtExtention)
        {
            if (dtExtention == null)
                return;

             foreach (DataRow row in dtExtention.Rows)
            {
                IData data = GetDataObject();
                string sqlText = @"INSERT INTO [SemiCaseDB].[dbo].[EXTENTION]
                                       ([ExtentionID]
                                       ,[Order]
                                       ,[Name]
                                       ,OrderSign)
                                 VALUES
                                       (@ExtentionID
                                       ,@Order
                                       ,@Name
                                       ,@OrderSign);

                                INSERT INTO [SemiCaseDB].[dbo].[RELATION]
                                       ([RelationID]
                                       ,[ProjectID]
                                       ,[UseCaseID]
                                       ,[MssID]
                                       ,[ExtentionID])
                                 VALUES
                                       (NEWID()
                                       ,@ProjectID
                                       ,@UseCaseID
                                       ,@MssID
                                       ,@ExtentionID)";

                data.AddSqlParameter("ProjectID", pProjectID, SqlDbType.UniqueIdentifier);
                data.AddSqlParameter("UseCaseID", pUseCaseID, SqlDbType.UniqueIdentifier);
                data.AddSqlParameter("ExtentionID", row["ExtentionID"], SqlDbType.UniqueIdentifier);
                data.AddSqlParameter("Order", row["SiraNo"], SqlDbType.VarChar);
                data.AddSqlParameter("MssID", row["MssID"], SqlDbType.UniqueIdentifier);
                data.AddSqlParameter("Name", row["Name"], SqlDbType.VarChar);
                data.AddSqlParameter("OrderSign", row["OrderSign"], SqlDbType.VarChar);

                data.ExecuteStatement(sqlText);
            }
        }

    }
}
